﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using KPIS.GERP.GBMS.Reports;
using KPIS.GERP.GBMS.MODEL;
using KPIS.DBM;
//using Telerik.ReportViewer;
using KPIS.GERP.GBMS.BLL;
using KPIS.GERP.GBMS.IBLL;


namespace KPIS.GERP.GBMS.APP_UI.Form
{
    public partial class ReportViewer : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
           Telerik.Reporting.ObjectDataSource objectDataSource = new Telerik.Reporting.ObjectDataSource();
           objectDataSource.DataSource = GetData();

            //DataSet1 ds = new DataSet1();
            //ds.Report_202.Load(GetData());

            Report_202_new rpt = new Report_202_new();
            rpt.DataSource = objectDataSource;
            
            Telerik.Reporting.InstanceReportSource reportSource = new Telerik.Reporting.InstanceReportSource();
            reportSource.ReportDocument = rpt;
            ReportViewer1.ReportSource = reportSource;
            ReportViewer1.DataBind();
        }
        protected DataTable GetData()
        {
            //string strRecordStatus = rblRecordStatus.SelectedValue;
            //IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            //DataTable ResultDTB = new DataTable();
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            DataTable ResultDTB = new DataTable();
            DataTable dtb;
            dbManager.Open();

            try
            {
                string strSQL = "select r.bgm_req_seq, r.bgm_req_name"
                        + ", r.date_start, r.date_end, j.job_site_name, r.bgm_req_remark"

                        + ", re1.amount1, re2.amount2, re3.amount3"
                        + ", re4.amount4, re5.amount5, re6.amount6"
                        + ", re7.amount7"

                        + " from bgm_request r"
                        + " left join (select re.bgm_req_seq"
                            + ", sum(re.total_amount) as amount1"
                            + " from bgm_request_expense re"
                            + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                            + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                            + " where re.bgm_source_seq = 1"
                            + " and gexp.bgm_exp_grp_seq = 1"
                            + " group by re.bgm_req_seq"
                            + ") re1 on r.bgm_req_seq = re1.bgm_req_seq"
                        + " left join (select re.bgm_req_seq"
                            + ", sum(re.total_amount) as amount2"
                            + " from bgm_request_expense re"
                            + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                            + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                            + " where re.bgm_source_seq = 1"
                            + " and gexp.bgm_exp_grp_seq = 2"
                            + " group by re.bgm_req_seq"
                            + ") re2 on r.bgm_req_seq = re2.bgm_req_seq"
                        + " left join (select re.bgm_req_seq"
                            + ", sum(re.total_amount) as amount3"
                            + " from bgm_request_expense re"
                            + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                            + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                            + " where re.bgm_source_seq = 1"
                            + " and gexp.bgm_exp_grp_seq = 3"
                            + " group by re.bgm_req_seq"
                            + ") re3 on r.bgm_req_seq = re3.bgm_req_seq"
                        + " left join (select re.bgm_req_seq"
                            + ", sum(re.total_amount) as amount4"
                            + " from bgm_request_expense re"
                            + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                            + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                            + " where re.bgm_source_seq = 1"
                            + " and gexp.bgm_exp_grp_seq = 4"
                            + " group by re.bgm_req_seq"
                            + ") re4 on r.bgm_req_seq = re4.bgm_req_seq"
                        + " left join (select re.bgm_req_seq"
                            + ", sum(re.total_amount) as amount5"
                            + " from bgm_request_expense re"
                            + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                            + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                            + " where re.bgm_source_seq = 1"
                            + " and gexp.bgm_exp_grp_seq = 5"
                            + " group by re.bgm_req_seq"
                            + ") re5 on r.bgm_req_seq = re5.bgm_req_seq"
                        + " left join (select re.bgm_req_seq"
                            + ", sum(re.total_amount) as amount6"
                            + " from bgm_request_expense re"
                            + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                            + " where re.bgm_source_seq = 6"
                            + " group by re.bgm_req_seq"
                            + ") re6 on r.bgm_req_seq = re6.bgm_req_seq"
                        + " left join (select re.bgm_req_seq"
                            + ", sum(re.total_amount) as amount7"
                            + " from bgm_request_expense re"
                            + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                            + " where re.bgm_source_seq = 7"
                            + " group by re.bgm_req_seq"
                            + ") re7 on r.bgm_req_seq = re7.bgm_req_seq"
                        + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"

                        //+ " where r.record_status = '" + rblRecordStatus.SelectedValue + "'"
                     + " where 0 = 0"
                     + " and r.record_status = 'A'"
                     + " and r.bgm_year = 2555";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch
            {
                return null;
            }


          /*(   try
             {
                 string strSQL = "select r.bgm_req_seq, r.bgm_req_name"
                     + ", r.date_start, r.date_end, j.job_site_name, r.bgm_req_remark" 

                     + ", re1.amount1, re2.amount2, re3.amount3"
                     + ", re4.amount4, re5.amount5, re6.amount6"
                     + ", re7.amount7"

                     + " from bgm_request r"
                     + " left join (select re.bgm_req_seq"
                         + ", sum(re.total_amount) as amount1"
                         + " from bgm_request_expense re"
                         + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                         + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                         + " where re.bgm_source_seq = 1"
                         + " and gexp.bgm_exp_grp_seq = 1"
                         + " group by re.bgm_req_seq"
                         + ") re1 on r.bgm_req_seq = re1.bgm_req_seq"
                     + " left join (select re.bgm_req_seq"
                         + ", sum(re.total_amount) as amount2"
                         + " from bgm_request_expense re"
                         + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                         + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                         + " where re.bgm_source_seq = 1"
                         + " and gexp.bgm_exp_grp_seq = 2"
                         + " group by re.bgm_req_seq"
                         + ") re2 on r.bgm_req_seq = re2.bgm_req_seq"
                     + " left join (select re.bgm_req_seq"
                         + ", sum(re.total_amount) as amount3"
                         + " from bgm_request_expense re"
                         + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                         + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                         + " where re.bgm_source_seq = 1"
                         + " and gexp.bgm_exp_grp_seq = 3"
                         + " group by re.bgm_req_seq"
                         + ") re3 on r.bgm_req_seq = re3.bgm_req_seq"
                     + " left join (select re.bgm_req_seq"
                         + ", sum(re.total_amount) as amount4"
                         + " from bgm_request_expense re"
                         + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                         + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                         + " where re.bgm_source_seq = 1"
                         + " and gexp.bgm_exp_grp_seq = 4"
                         + " group by re.bgm_req_seq"
                         + ") re4 on r.bgm_req_seq = re4.bgm_req_seq"
                     + " left join (select re.bgm_req_seq"
                         + ", sum(re.total_amount) as amount5"
                         + " from bgm_request_expense re"
                         + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                         + " left join bgm_mas_expense_group gexp on exp.bgm_exp_grp_seq = gexp.bgm_exp_grp_seq"
                         + " where re.bgm_source_seq = 1"
                         + " and gexp.bgm_exp_grp_seq = 5"
                         + " group by re.bgm_req_seq"
                         + ") re5 on r.bgm_req_seq = re5.bgm_req_seq"
                     + " left join (select re.bgm_req_seq"
                         + ", sum(re.total_amount) as amount6"
                         + " from bgm_request_expense re"
                         + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                         + " where re.bgm_source_seq = 6"
                         + " group by re.bgm_req_seq"
                         + ") re6 on r.bgm_req_seq = re6.bgm_req_seq"
                     + " left join (select re.bgm_req_seq"
                         + ", sum(re.total_amount) as amount7"
                         + " from bgm_request_expense re"
                         + " left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                         + " where re.bgm_source_seq = 7"
                         + " group by re.bgm_req_seq"
                         + ") re7 on r.bgm_req_seq = re7.bgm_req_seq"
                     + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"

                     //+ " where r.record_status = '" + rblRecordStatus.SelectedValue + "'"
                     + " where 0 = 0"
                     + " and r.record_status = 'A'"
                     + " and r.bgm_year = 2555";
                 dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                 return dtb;
             }
             catch
             {
                 return null;
             }*/


        }
    }
}